from typing import List, Optional, Dict, Any
from sqlalchemy.orm import Session
from sqlalchemy import or_
from datetime import datetime, timedelta

from app.models.learning import KnowledgeBase, Exercise, LearningPlan, LearningTask, Progress, user_knowledge_enrollments
from app.models.user import User
from app.schemas.learning import (
    KnowledgeBaseCreate, KnowledgeBaseUpdate,
    ExerciseCreate, ExerciseUpdate,
    LearningPlanCreate, LearningPlanUpdate,
    LearningTaskCreate, LearningTaskUpdate,
    ProgressCreate
)

def create_knowledge(db: Session, *, obj_in: KnowledgeBaseCreate) -> KnowledgeBase:
    """创建知识库条目"""
    # 如果知识点为空，则使用内容前100个字符作为知识点
    if not obj_in.knowledge_point and obj_in.content:
        knowledge_point = obj_in.content[:100]
    else:
        knowledge_point = obj_in.knowledge_point

    db_obj = KnowledgeBase(
        subject=obj_in.subject,
        level=obj_in.level,
        chapter=obj_in.chapter,
        knowledge_point=knowledge_point,
        content=obj_in.content,
        study_count=0
    )
    db.add(db_obj)
    db.commit()
    db.refresh(db_obj)
    return db_obj

def get_knowledge(db: Session, knowledge_id: int) -> Optional[KnowledgeBase]:
    """获取单个知识库条目"""
    return db.query(KnowledgeBase).filter(KnowledgeBase.id == knowledge_id).first()

def get_knowledge_multi(
    db: Session,
    *,
    skip: int = 0,
    limit: int = 100,
    subject: Optional[str] = None,
    level: Optional[str] = None,
    chapter: Optional[str] = None
) -> List[KnowledgeBase]:
    """获取多个知识库条目"""
    try:
        # 确保分页参数合法
        skip = max(0, skip)
        limit = min(100, max(1, limit))  # 限制每页最大条数为100

        # 构建查询
        query = db.query(KnowledgeBase)

        # 添加过滤条件
        if subject and subject.strip():
            query = query.filter(KnowledgeBase.subject == subject.strip())
        if level and level.strip():
            query = query.filter(KnowledgeBase.level == level.strip())
        if chapter and chapter.strip():
            query = query.filter(KnowledgeBase.chapter == chapter.strip())

        # 先打印一下查询语句，帮助调试
        print(f"SQL Query: {query}")

        # 执行查询
        results = query.offset(skip).limit(limit).all()

        # 打印结果数量，帮助调试
        print(f"Query returned {len(results)} results")

        # 检查表中是否有数据
        if len(results) == 0:
            total_count = db.query(KnowledgeBase).count()
            print(f"Total records in knowledge_base: {total_count}")
            if total_count == 0:
                print("Warning: knowledge_base table is empty!")

        return results
    except Exception as e:
        import traceback
        print(f"Error in get_knowledge_multi: {e}")
        print(f"Traceback: {traceback.format_exc()}")
        # 返回空列表而不是抛出异常，避免500错误
        return []

def search_knowledge(
    db: Session,
    *,
    keyword: str,
    skip: int = 0,
    limit: int = 100
) -> List[KnowledgeBase]:
    """搜索知识库"""
    return db.query(KnowledgeBase).filter(
        or_(
            KnowledgeBase.subject.ilike(f"%{keyword}%"),
            KnowledgeBase.chapter.ilike(f"%{keyword}%"),
            KnowledgeBase.knowledge_point.ilike(f"%{keyword}%"),
            KnowledgeBase.content.ilike(f"%{keyword}%")
        )
    ).offset(skip).limit(limit).all()

def update_knowledge(
    db: Session,
    *,
    db_obj: KnowledgeBase,
    obj_in: KnowledgeBaseUpdate
) -> KnowledgeBase:
    """更新知识库条目"""
    update_data = obj_in.model_dump(exclude_unset=True)

    # 如果更新了content但没有更新knowledge_point，则自动更新knowledge_point
    if "content" in update_data and "knowledge_point" not in update_data:
        update_data["knowledge_point"] = update_data["content"][:100]

    for field in update_data:
        setattr(db_obj, field, update_data[field])

    db.add(db_obj)
    db.commit()
    db.refresh(db_obj)
    return db_obj

def delete_knowledge(db: Session, *, knowledge_id: int) -> KnowledgeBase:
    """删除知识库条目"""
    obj = db.query(KnowledgeBase).get(knowledge_id)
    db.delete(obj)
    db.commit()
    return obj

def increment_study_count(db: Session, *, knowledge_id: int) -> KnowledgeBase:
    """增加学习次数"""
    obj = db.query(KnowledgeBase).get(knowledge_id)
    obj.study_count += 1
    db.add(obj)
    db.commit()
    db.refresh(obj)
    return obj

# Exercise CRUD operations
def create_exercise(db: Session, *, obj_in: ExerciseCreate) -> Exercise:
    """创建习题"""
    db_obj = Exercise(**obj_in.model_dump())
    db.add(db_obj)
    db.commit()
    db.refresh(db_obj)
    return db_obj

def get_exercises_by_knowledge(
    db: Session,
    *,
    knowledge_id: int,
    skip: int = 0,
    limit: int = 100
) -> List[Exercise]:
    """获取知识点相关的习题"""
    return db.query(Exercise).filter(
        Exercise.knowledge_id == knowledge_id
    ).offset(skip).limit(limit).all()

def update_exercise(
    db: Session,
    *,
    db_obj: Exercise,
    obj_in: ExerciseUpdate
) -> Exercise:
    """更新习题"""
    update_data = obj_in.model_dump(exclude_unset=True)
    for field in update_data:
        setattr(db_obj, field, update_data[field])
    db.add(db_obj)
    db.commit()
    db.refresh(db_obj)
    return db_obj

def increment_exercise_stats(
    db: Session,
    *,
    exercise_id: int,
    is_correct: bool
) -> Exercise:
    """更新习题的答题统计"""
    obj = db.query(Exercise).get(exercise_id)
    obj.attempt_count += 1
    if is_correct:
        obj.correct_count += 1
    db.add(obj)
    db.commit()
    db.refresh(obj)
    return obj

# Learning Plan CRUD operations
def create_learning_plan(
    db: Session,
    *,
    plan_in: LearningPlanCreate,
    user_id: int,
    knowledge_base_id: Optional[int] = None
) -> LearningPlan:
    """创建学习计划，支持基于知识库/课程创建"""
    plan_data = plan_in.model_dump()

    # 如果指定了知识库/课程ID，从中获取基本信息
    if knowledge_base_id:
        knowledge = db.query(KnowledgeBase).filter(KnowledgeBase.id == knowledge_base_id).first()
        if knowledge:
            if not plan_data.get("title"):
                title_text = knowledge.title if knowledge.is_course else knowledge.knowledge_point
                plan_data["title"] = f"{title_text}学习计划"
            if not plan_data.get("description"):
                desc_text = knowledge.title if knowledge.is_course else knowledge.knowledge_point
                plan_data["description"] = f"基于《{desc_text}》生成的学习计划"
            if not plan_data.get("subject"):
                plan_data["subject"] = knowledge.subject
            plan_data["knowledge_base_id"] = knowledge_base_id

            # 设置默认开始和结束时间
            if not plan_data.get("start_date"):
                plan_data["start_date"] = datetime.utcnow()
            if not plan_data.get("end_date") and knowledge.duration and knowledge.is_course:
                # 根据课程时长设置结束时间，假设每天学习2小时
                days_needed = knowledge.duration / 2
                plan_data["end_date"] = plan_data["start_date"] + timedelta(days=days_needed)

    db_obj = LearningPlan(**plan_data, user_id=user_id)
    db.add(db_obj)
    db.commit()
    db.refresh(db_obj)

    # 如果是基于知识库/课程创建，自动生成学习任务
    if knowledge_base_id and db_obj.id:
        create_tasks_from_knowledge(db, plan_id=db_obj.id, knowledge_base_id=knowledge_base_id)

    return db_obj

def create_tasks_from_knowledge(
    db: Session,
    *,
    plan_id: int,
    knowledge_base_id: int
) -> List[LearningTask]:
    """基于知识库/课程内容创建学习任务"""
    # 获取知识点
    knowledge_base = db.query(KnowledgeBase).filter(
        KnowledgeBase.id == knowledge_base_id
    ).first()

    if not knowledge_base:
        return []

    tasks = []

    # 如果是课程，查找相关的知识点
    if knowledge_base.is_course:
        # 查找同一学科同一级别的知识点作为学习任务
        related_knowledge = db.query(KnowledgeBase).filter(
            KnowledgeBase.subject == knowledge_base.subject,
            KnowledgeBase.level == knowledge_base.level,
            KnowledgeBase.is_course == False  # 不是课程的知识点
        ).all()

        for idx, kp in enumerate(related_knowledge, 1):
            # 为每个知识点创建学习任务
            task = LearningTask(
                plan_id=plan_id,
                title=f"学习任务 {idx}: {kp.chapter}",
                description=f"学习内容：{kp.knowledge_point}",
                priority=2,  # 默认中等优先级
                duration=60,  # 默认1小时
                status="pending"
            )
            db.add(task)
            tasks.append(task)
    else:
        # 如果是单个知识点，为其创建一个学习任务
        task = LearningTask(
            plan_id=plan_id,
            title=f"学习: {knowledge_base.chapter}",
            description=f"学习内容：{knowledge_base.knowledge_point}",
            priority=2,
            duration=60,
            status="pending"
        )
        db.add(task)
        tasks.append(task)

    db.commit()
    for task in tasks:
        db.refresh(task)

    return tasks

def get_learning_plan(
    db: Session,
    plan_id: int
) -> Optional[LearningPlan]:
    """获取单个学习计划"""
    return db.query(LearningPlan).filter(LearningPlan.id == plan_id).first()

def get_learning_plans(
    db: Session,
    *,
    user_id: int,
    skip: int = 0,
    limit: int = 100,
    status: Optional[str] = None
) -> List[LearningPlan]:
    """获取用户的学习计划列表"""
    query = db.query(LearningPlan).filter(LearningPlan.user_id == user_id)
    if status:
        query = query.filter(LearningPlan.status == status)
    return query.offset(skip).limit(limit).all()

def update_learning_plan(
    db: Session,
    *,
    db_obj: LearningPlan,
    obj_in: LearningPlanUpdate
) -> LearningPlan:
    """更新学习计划"""
    update_data = obj_in.model_dump(exclude_unset=True)
    for field in update_data:
        setattr(db_obj, field, update_data[field])
    db.add(db_obj)
    db.commit()
    db.refresh(db_obj)
    return db_obj

def delete_learning_plan(
    db: Session,
    *,
    plan_id: int
) -> LearningPlan:
    """删除学习计划"""
    obj = db.query(LearningPlan).get(plan_id)
    db.delete(obj)
    db.commit()
    return obj

# Learning Task CRUD operations
def create_task(
    db: Session,
    *,
    task_in: LearningTaskCreate
) -> LearningTask:
    """创建学习任务"""
    db_obj = LearningTask(**task_in.model_dump())
    db.add(db_obj)
    db.commit()
    db.refresh(db_obj)
    return db_obj

def get_task(
    db: Session,
    task_id: int
) -> Optional[LearningTask]:
    """获取单个任务"""
    return db.query(LearningTask).filter(LearningTask.id == task_id).first()

def get_plan_tasks(
    db: Session,
    *,
    plan_id: int,
    skip: int = 0,
    limit: int = 100,
    status: Optional[str] = None
) -> List[LearningTask]:
    """获取学习计划的任务列表"""
    query = db.query(LearningTask).filter(LearningTask.plan_id == plan_id)
    if status:
        query = query.filter(LearningTask.status == status)
    return query.offset(skip).limit(limit).all()

def update_task(
    db: Session,
    *,
    db_obj: LearningTask,
    obj_in: LearningTaskUpdate
) -> LearningTask:
    """更新任务"""
    update_data = obj_in.model_dump(exclude_unset=True)

    # 处理开始学习的情况
    if "start_date" in update_data and not db_obj.start_date:
        db_obj.start_date = update_data["start_date"]
        db_obj.due_date = update_data.get("due_date") or (db_obj.start_date + timedelta(hours=24))
        db_obj.status = "in_progress"

    for field in update_data:
        if field not in ["start_date", "due_date", "status"]:  # 避免重复设置
            setattr(db_obj, field, update_data[field])

    db.add(db_obj)

    # 更新计划的进度
    plan = db.query(LearningPlan).filter(LearningPlan.id == db_obj.plan_id).first()
    if plan:
        # 获取计划下所有任务
        all_tasks = db.query(LearningTask).filter(LearningTask.plan_id == plan.id).all()
        completed_tasks = len([t for t in all_tasks if t.status == "completed"])
        in_progress_tasks = len([t for t in all_tasks if t.status == "in_progress"])
        total_tasks = len(all_tasks)

        # 计算并更新进度
        if total_tasks > 0:
            progress = round(((completed_tasks + 0.5 * in_progress_tasks) / total_tasks) * 100)
            plan.progress = progress

            # 更新计划状态
            if progress == 100:
                plan.status = "completed"
            elif progress > 0:
                plan.status = "in_progress"
            db.add(plan)

    db.commit()
    db.refresh(db_obj)
    return db_obj

def delete_task(
    db: Session,
    *,
    task_id: int
) -> LearningTask:
    """删除任务"""
    obj = db.query(LearningTask).get(task_id)
    db.delete(obj)
    db.commit()
    return obj

# Progress CRUD operations
def create_progress(
    db: Session,
    *,
    progress_in: ProgressCreate,
    user_id: int
) -> Progress:
    """创建进度记录"""
    db_obj = Progress(**progress_in.model_dump(), user_id=user_id)
    db.add(db_obj)
    db.commit()
    db.refresh(db_obj)
    return db_obj

def get_task_progress(
    db: Session,
    *,
    task_id: int,
    skip: int = 0,
    limit: int = 100
) -> List[Progress]:
    """获取任务的进度记录"""
    return db.query(Progress).filter(
        Progress.task_id == task_id
    ).offset(skip).limit(limit).all()

def get_user_progress(
    db: Session,
    *,
    user_id: int,
    skip: int = 0,
    limit: int = 100
) -> List[Progress]:
    """获取用户的所有进度记录"""
    return db.query(Progress).filter(
        Progress.user_id == user_id
    ).offset(skip).limit(limit).all()

# 知识点选课相关功能
def get_knowledge_list(
    db: Session,
    *,
    skip: int = 0,
    limit: int = 100,
    subject: Optional[str] = None,
    level: Optional[str] = None
) -> List[KnowledgeBase]:
    """获取知识点列表，可按科目和级别筛选"""
    query = db.query(KnowledgeBase)
    if subject:
        query = query.filter(KnowledgeBase.subject == subject)
    if level:
        query = query.filter(KnowledgeBase.level == level)
    return query.offset(skip).limit(limit).all()

def enroll_knowledge(
    db: Session,
    *,
    user_id: int,
    knowledge_id: int
) -> bool:
    """用户选择学习特定知识点"""
    # 检查知识点是否存在
    knowledge = db.query(KnowledgeBase).filter(KnowledgeBase.id == knowledge_id).first()
    if not knowledge:
        return False

    # 检查用户是否已经选择了该知识点
    user = db.query(User).filter(User.id == user_id).first()
    if not user:
        return False

    # 检查是否已经选择过
    existing_enrollment = db.query(user_knowledge_enrollments).filter(
        user_knowledge_enrollments.c.user_id == user_id,
        user_knowledge_enrollments.c.knowledge_id == knowledge_id
    ).first()

    if existing_enrollment:
        # 如果已经选择过但状态是dropped，则更新状态为active
        if existing_enrollment.status == 'dropped':
            db.execute(
                user_knowledge_enrollments.update().where(
                    user_knowledge_enrollments.c.user_id == user_id,
                    user_knowledge_enrollments.c.knowledge_id == knowledge_id
                ).values(status='active', enrolled_at=datetime.utcnow())
            )
            db.commit()
            return True
        # 如果已经是active状态，不需要操作
        return True

    # 新增选课记录
    db.execute(
        user_knowledge_enrollments.insert().values(
            user_id=user_id,
            knowledge_id=knowledge_id,
            enrolled_at=datetime.utcnow(),
            status='active',
            progress=0.0
        )
    )
    db.commit()
    return True

def get_enrolled_knowledge(
    db: Session,
    *,
    user_id: int,
    skip: int = 0,
    limit: int = 100
) -> List[KnowledgeBase]:
    """获取用户已选择的知识点列表"""
    user = db.query(User).filter(User.id == user_id).first()
    if not user:
        return []

    # 获取用户选择的且状态为active的知识点
    return db.query(KnowledgeBase).join(
        user_knowledge_enrollments,
        KnowledgeBase.id == user_knowledge_enrollments.c.knowledge_id
    ).filter(
        user_knowledge_enrollments.c.user_id == user_id,
        user_knowledge_enrollments.c.status == 'active'
    ).offset(skip).limit(limit).all()

def cancel_knowledge_enrollment(
    db: Session,
    *,
    user_id: int,
    knowledge_id: int
) -> bool:
    """取消用户选择的知识点"""
    # 检查知识点是否存在
    knowledge = db.query(KnowledgeBase).filter(KnowledgeBase.id == knowledge_id).first()
    if not knowledge:
        return False

    # 检查用户是否存在
    user = db.query(User).filter(User.id == user_id).first()
    if not user:
        return False

    # 检查是否已经选择过
    existing_enrollment = db.query(user_knowledge_enrollments).filter(
        user_knowledge_enrollments.c.user_id == user_id,
        user_knowledge_enrollments.c.knowledge_id == knowledge_id,
        user_knowledge_enrollments.c.status == 'active'
    ).first()

    if not existing_enrollment:
        # 如果没有选择过或已经取消，不需要操作
        return True

    # 更新选课记录状态为dropped
    db.execute(
        user_knowledge_enrollments.update().where(
            user_knowledge_enrollments.c.user_id == user_id,
            user_knowledge_enrollments.c.knowledge_id == knowledge_id
        ).values(status='dropped')
    )
    db.commit()
    return True

def generate_learning_plan(
    db: Session,
    *,
    user_id: int,
    knowledge_ids: List[int]
) -> Optional[LearningPlan]:
    """基于选择的知识点生成学习计划"""
    # 检查用户是否存在
    user = db.query(User).filter(User.id == user_id).first()
    if not user:
        return None

    # 获取所有选择的知识点
    knowledge_points = db.query(KnowledgeBase).filter(
        KnowledgeBase.id.in_(knowledge_ids)
    ).all()

    if not knowledge_points:
        return None

    # 确定学习计划的主题和级别
    subjects = set(kp.subject for kp in knowledge_points)
    levels = set(kp.level for kp in knowledge_points)

    # 创建学习计划
    plan_title = f"{'、'.join(subjects)}学习计划"
    plan_description = f"基于{'、'.join(subjects)}知识点生成的学习计划"

    plan = LearningPlan(
        user_id=user_id,
        title=plan_title,
        description=plan_description,
        subject=", ".join(subjects),
        grade_level=", ".join(levels) if levels else None,
        start_date=datetime.utcnow(),
        end_date=datetime.utcnow() + timedelta(days=30),  # 默认30天学习计划
        status="active"
    )

    db.add(plan)
    db.commit()
    db.refresh(plan)

    # 为每个知识点创建学习任务
    for idx, kp in enumerate(knowledge_points, 1):
        task = LearningTask(
            plan_id=plan.id,
            title=f"学习任务 {idx}: {kp.chapter or kp.knowledge_point[:20]}",
            description=f"学习内容：{kp.knowledge_point}",
            priority=2,  # 默认中等优先级
            duration=60,  # 默认1小时
            due_date=datetime.utcnow() + timedelta(days=idx),  # 按顺序设置截止日期
            status="pending"
        )
        db.add(task)

    db.commit()
    return plan